import sqlite3
import os
import re

class SQLiteHelper:
    def __init__(self,path):

        if not os.path.exists(path):
            print('db file is not found!')
        # print(path)
        self.conn = sqlite3.connect(path)
        self.conn.row_factory = sqlite3.Row


    def get_tables(self):
        # 创建连接
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute("SELECT * FROM sqlite_master WHERE type='table' ORDER BY name;")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = [dict(row) for row in rows]
        return data_list

    def get_colum_list(self,name):
        # 创建连接
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(f"PRAGMA table_info({name})")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = []
        for row in rows:
            len = ''
            str_type = row['type']
            match = re.search(r'\((\d+)\)',str_type)
            if match:
                len= match.group(1)   # 返回匹配的数字，并转换为整数
                str_type = str_type.split('(')[0]

            data = {
                "col_type": str_type,
                "defval": None,
                "desc": "",
                "len": len,
                "name": row["name"],
                "nullable": 0 if row["notnull"] else 1,
                "pk": row["pk"]
            }


            data_list.append(data)

        return data_list

    def __del__(self):
        self.conn.close()